import decimal

import pymysql

from config.env_config import env_config
from utils.logger import logger
from pymysql import converters


class __DBUtil(object):

    def __init__(self, host, port, user, passwd):
        # 处理mysql select的bit类型字段返回不为\x00
        converions = converters.conversions
        converions[pymysql.FIELD_TYPE.BIT] = lambda x: '0' if '\x00' else '1'
        self.conn = pymysql.connect(
            host=host,
            port=int(port),
            user=user,
            passwd=passwd,
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor,
            conv=converions
        )
        self.cursor = self.conn.cursor()

    def __del__(self):
        try:
            self.conn.close()
        except:
            pass

    def close(self):
        try:
            self.conn.close()
        except:
            logger.error("数据库链接关闭失败")

    def execute(self, sql, agrs=[]):
        try:
            self.cursor.execute(sql, agrs)
            logger.info("execute sql:%s, agrs: %s" % (sql, agrs))
        except:
            logger.info("execute sql:%s, agrs: %s" % (sql, agrs))
            self.conn.ping()  # reconnecting mysql
            self.cursor = self.conn.cursor()
            self.cursor.execute(sql, agrs)
        self.conn.commit()
        # logger.info("execute sql:%s" % sql)

    def query(self, sql, agrs=[]):
        try:
            self.cursor.execute(sql, agrs)
            logger.info('query sql: %s, agrs: %s' % (sql, agrs))
        except:
            logger.info('query sql: %s, agrs: %s' % (sql, agrs))
            self.conn.ping()  # reconnecting mysql
            self.cursor = self.conn.cursor()
            self.cursor.execute(sql, agrs)
        # logger.info('query sql: %s' % sql)
        return self.cursor.fetchall()


class SqlFormat:
    def selectNormal(self, table, data='*', params={}, end=''):
        """
        简单单表查询
        """
        # dataStr = ','.join(data)
        paramsValue = []
        sql = "select %s from %s" % (data, table)
        if params:
            sql = "%s where" % sql
            for i, key in enumerate(params):
                if i == 0:
                    sql = "%s %s=%s" % (sql, key, "%s")
                    paramsValue.append(params[key])
                else:
                    sql = "%s and %s=%s" % (sql, key, "%s")
                    paramsValue.append(params[key])
            if end:
                sql = "%s %s" % (sql, end)
        return [sql, paramsValue]


class DBObject(__DBUtil):
    def __init__(self):
        db_user = env_config.ENV_MAP["db_user"]
        db_password = env_config.ENV_MAP["db_password"]
        db_ip = env_config.ENV_MAP["db_ip"]
        db_port = env_config.ENV_MAP["db_port"]
        super(DBObject, self).__init__(db_ip, db_port, db_user, db_password)

    def selectOneTable(self, tablename, **kwargs):
        '''单表查询'''
        sql = "select * from %s" % tablename
        paramList = []
        if len(kwargs) > 0:
            sql = "%s where" % sql
            for i, kw in enumerate(kwargs):
                if i == 0:
                    sql = "%s %s=%s" % (sql, kw, '%s')
                    paramList.append(kwargs[kw])
                else:
                    sql = "%s and %s=%s" % (sql, kw, '%s')
                    paramList.append(kwargs[kw])
        return self.query(sql, paramList)

    def selectOne(self, tablename, **kwargs):
        """查询一条记录"""
        result = self.selectOneTable(tablename, **kwargs)
        if len(result) > 0:
            return result[0]
        else:
            return None

    def insertData(self, table, data):
        """处理insert 字段过多，导致对应关系混乱"""
        if table is None or data is None:
            raise RuntimeError("table和data都不能为空！！！")
        # 将data处理成key-value,拼装sql
        sql = "insert ignore into " + table
        keys = ' ('
        values = ' ('
        for key, value in data.items():
            if keys == ' (':
                if value is None:
                    continue
                keys = keys + key
                if type(value) is int or type(value) is float or type(value) is bool:
                    values = values + str(value)
                elif type(value) is decimal.Decimal:
                    values = values + str(decimal.Decimal(value))
                else:
                    values = values + "'" + value + "'"
            else:
                if value is None:
                    continue
                keys = keys + "," + key
                if type(value) is int or type(value) is float or type(value) is bool:
                    values = values + "," + str(value)
                elif type(value) is decimal.Decimal:
                    values = values + "," + str(decimal.Decimal(value))
                else:
                    values = values + "," + "'" + value + "'"
        keys = keys + ")"
        values = values + ")"
        sql = sql + keys + " VALUES " + values
        self.execute(sql)


db = DBObject()